package Mysql;

import Message.Tips;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库查询类
 */
public class Query {

    /**
     * 获取房间音乐列表数量
     * @param room 房间号
     * @return 数量
     */
    public static int getRoomMusicCount(String room){
        java.util.Date startDate = new java.util.Date();
        String sql = "SELECT COUNT(*) FROM room_music_" + room + ";";
        Connection connection = Mysql.Start.getConnection();
        Statement statement = null;
        int count = -1;
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            if (resultSet.next()){//有数据
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("房间音乐列表(" + room + ")查询数量失败！");
            Mysql.Start.reConnection();//重连数据库
            return -1;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间音乐列表(" + room + ")查询数量成功！耗时：" + span + "毫秒");
        return count;
    }

    /**
     * 获取房间弹幕
     */
    public static List<Object> getRoomDoomm(String room){
        java.util.Date startDate = new java.util.Date();
        String sql = "select * from room_Doomm_" + room + ";";
        Connection connection = Mysql.Start.getConnection();
        Statement statement = null;
        List<Object> reList = new ArrayList<>();
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){//有数据
                Map<String , Object> reMap = new HashMap<>(12);
                reMap.put("id",resultSet.getInt("id"));
                reMap.put("openid",resultSet.getString("openid"));
                reMap.put("nickName",resultSet.getString("nickName"));
                reMap.put("content",resultSet.getString("content"));
                reMap.put("regDate" , resultSet.getTimestamp("regDate"));
                reList.add(reMap);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("房间音乐列表查询(" + room + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return null;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间音乐列表查询(" + room + ")成功！耗时：" + span + "毫秒");
        return reList;
    }


    /**
     * 获取房间音乐列表
     */
    public static List<Object> getRoomMusic(String room){
        java.util.Date startDate = new java.util.Date();
        String sql = "select * from room_music_" + room + ";";
        Connection connection = Mysql.Start.getConnection();
        Statement statement = null;
        List<Object> reList = new ArrayList<>();
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){//有数据
                Map<String , Object> reMap = new HashMap<>(12);
                reMap.put("id",resultSet.getInt("id"));
                reMap.put("musicId",resultSet.getString("musicId"));
                reMap.put("name",resultSet.getString("name"));
                reMap.put("artists",resultSet.getString("artists"));
                reMap.put("duration",resultSet.getInt("duration"));
                reMap.put("imageUrl",resultSet.getString("imageUrl"));
                reMap.put("url",resultSet.getString("url"));
                reMap.put("vip",resultSet.getBoolean("vip"));
                reMap.put("regDate" , resultSet.getTimestamp("regDate"));
                reList.add(reMap);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("房间音乐列表查询(" + room + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return null;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间音乐列表查询(" + room + ")成功！耗时：" + span + "毫秒");
        return reList;
    }

    /**
     * 获取房间基本信息
     */
    public static Map<String , Object> getRoomBase(String room){
        java.util.Date startDate = new java.util.Date();
        String sql = "select * from room_base_" + room + ";";
        Connection connection = Mysql.Start.getConnection();
        Statement statement = null;
        Map<String , Object> reMap = new HashMap<>();
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            if (resultSet.next()){//有数据
                reMap.put("regDate" , resultSet.getTimestamp("regDate"));
                reMap.put("updDate" , resultSet.getTimestamp("updDate"));
                reMap.put("musicDate" , resultSet.getTimestamp("musicDate"));
            }else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("房间基础查询(" + room + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return null;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间基础查询(" + room + ")成功！耗时：" + span + "毫秒");
        return reMap;
    }

    /**
     * 查询表是否存在
     */
    public static boolean isExistsTable(String table){
        java.util.Date startDate = new java.util.Date();
        String sql = "select * from information_schema.tables where table_name ='" + table + "';";
        Connection connection = Mysql.Start.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            if (!resultSet.next()){//没有数据
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("表存在查询(" + table + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("表存在查询(" + table + ")成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 获取房间用户数量
     * @param room 房间号
     * @return 数量
     */
    public static int getRoomUserCount(String room){
        java.util.Date startDate = new java.util.Date();
        String sql = "SELECT COUNT(*) FROM room_user_" + room + ";";
        Connection connection = Mysql.Start.getConnection();
        Statement statement = null;
        int count = -1;
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            if (resultSet.next()){//有数据
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("房间(" + room + ")查询数量失败！");
            Mysql.Start.reConnection();//重连数据库
            return -1;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间(" + room + ")查询数量成功！耗时：" + span + "毫秒");
        return count;
    }

    /**
     * 获取用户单个信息
     * @return null则查询异常 ， 空则没有数据
     */
    public static Map<String ,Object> getUser(String openid){
        java.util.Date startDate = new java.util.Date();
        String sql = "select * from userinfo where openid = ?;";
        Connection connection = Mysql.Start.getConnection();
        PreparedStatement preparedStatement = null;
        Map<String , Object> reMap = new HashMap<>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1 , openid);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){//有数据
                reMap.put("id" , resultSet.getInt("id"));
                reMap.put("openid" , openid);
                reMap.put("nickName" , resultSet.getString("nickName"));
                reMap.put("headimg" , resultSet.getString("headimg"));
                reMap.put("room" , resultSet.getInt("room"));
                reMap.put("cookie" , resultSet.getString("cookie"));
                reMap.put("regDate" , resultSet.getTimestamp("regDate"));
                reMap.put("updDate" , resultSet.getTimestamp("updDate"));
            }else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("用户(" + openid + ")详查询失败！");
            Mysql.Start.reConnection();//重连数据库
            return null;
        }finally {
            try {
                if(preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("用户(" + openid + ")详查询成功！耗时：" + span + "毫秒");
        return reMap;
    }


}
